#!/bin/bash
##
# Date: 2018-03-06
# Author: phiix
# Describe: Backup large data table,like table above 2G size.
# first: dump table structure.
# second: dump table data.
##

export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
export LANG=en_US.UTF-8

if [ $# -ne 2 ];then
    echo "Usage: $0 <database_name> <table_name>"
    exit 1
fi

database_name=$1
table_name=$2

alias mysql='mysql -S /var/lib/mysql/mysql.sock'

# check databases and tables
mysql -BN -e "use ${database_name};show tables;" 2>/dev/null |grep -Pq ${table_name} || { echo "no database ${database_name} or table ${table_name} . Exit..." ; exit 1 ; }

sql_dir=$(mysql -BN -e "show variables like '%secure_file_priv%';"|awk '{print $2}')
chown -R mysql.mysql ${sql_dir}

backup_dir=/data/backup_dir
mkdir -p ${backup_dir} && chown -R mysql.mysql ${backup_dir}

# dump table no data
echo "$(date +%F\ %T) == Start dump table: ${table_name} with no data. =="
alias mysqldump='mysqldump -S /var/lib/mysql/mysql.sock'
mysqldump -d ${database_name} ${table_name} > ${sql_dir}/${table_name}_db0.sql
if [ $? -ne 0 ];then
    echo "dump ${table_name} no data error.Exit..."
    exit 1
fi
unalias mysqldump
mv ${sql_dir}/${table_name}_db0.sql ${backup_dir}
echo "$(date +%F\ %T) == Complete,OK! =="

# dump table data
echo "$(date +%F\ %T) == Start dump table: ${table_name} with only data. =="
index=$(mysql -BN -e "describe ${database_name}.${table_name};"|grep -i "PRI*"|awk '{print $1}') 
echo "----"
start_id=0
id_step=5000000
end_id=$((start_id + id_step))
max_id=$(($(mysql -NB -e "select ${index} from ${database_name}.${table_name} order by ${index} desc limit 1;") + 1))
#max_id=30000000
while [ $end_id -le $max_id ];do
    echo "$start_id ----> $end_id"
    mysql -BN -e "select * from ${database_name}.${table_name} where ${index}>=${start_id} and ${index}<${end_id} into outfile \"${sql_dir}/${index}_${start_id}.txt\" fields terminated by ',' \
optionally enclosed by '\"' lines terminated by '\n';" 2>/dev/null
if [ $? -eq 0 ];then 
    mv ${sql_dir}/${index}_${start_id}.txt ${backup_dir}
else
    echo "[==>] ${start_id} ---> ${end_id} dump failed."
    echo "retry 3 times"
    times=1
    while [ $times -le 3 ]   
    do
	echo "times: ${times} , ${start_id} --->> ${end_id}"
	[ -f "${sql_dir}/${index}_${start_id}.txt" ] && rm -f "${sql_dir}/${index}_${start_id}.txt"
    mysql -BN -e "select * from ${database_name}.${table_name} where ${index}>=${start_id} and ${index}<${end_id} into outfile \"${sql_dir}/${index}_${start_id}.txt\" fields terminated by ',' \
optionally enclosed by '\"' lines terminated by '\n';" 2>/dev/null
	if [ $? -eq 0 ];then
	    echo "retry complete,ok!"
	    mv ${sql_dir}/${index}_${start_id}.txt ${backup_dir}
	    break
	else
	    echo "this time dump failed."
	fi
	sleep 5
	times=$(( times + 1 ))
    done
fi
    echo "----"
    start_id=$end_id
    end_id=$((end_id + id_step))
    sleep 10
done

unalias mysql
echo "$(date +%F\ %T) == Complete,OK! =="

exit 0
